package com.yidu.t299.dao.chr;

import com.yidu.t299.domain.chr.Grade;
import com.yidu.t299.domain.chr.Lesson;
import com.yidu.t299.domain.chr.StudentManage;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;

@Mapper
public interface GradeMapper {
    /**
     * 根据主键删除成绩信息
     * @param gradeId 主键
     * @return 影响的行数(1:成功)
     */
    @Delete({
            "delete from grade",
            " where grade_id = #{gradeId,jdbcType=INTEGER}"
    })
    int deleteByPrimaryKey(Integer gradeId);

    /**
     * 添加成绩信息，因为实体类不能对应，所以要分开传参
     * @param gradeId 成绩编号
     * @param studentId 学员编号
     * @param lessonId 课程编号
     * @param lessonGrade 课程成绩
     * @return 影响的行数(1:成功)
     */
    @Insert({
            "insert into grade (grade_id, student_id, ",
            "lesson_id, lesson_grade)",
            " values (#{gradeId,jdbcType=INTEGER}, #{studentId,jdbcType=INTEGER}, ",
            "#{lessonId,jdbcType=INTEGER}, #{lessonGrade,jdbcType=INTEGER})"
    })
    int insert(Integer gradeId,Integer studentId,Integer lessonId,Integer lessonGrade);

    /**
     * 按主键查询
     * @param gradeId 主键
     * @return 成绩对象
     */
    @Select({
            "select",
            " grade_id, student_id, lesson_id, lesson_grade",
            " from grade",
            " where grade_id = #{gradeId,jdbcType=INTEGER}"
    })
    @Results({
            @Result(column = "grade_id", property = "gradeId", jdbcType = JdbcType.INTEGER, id = true),
            @Result(column = "student_id", property = "studentId", jdbcType = JdbcType.INTEGER),
            @Result(column = "lesson_id", property = "lessonId", jdbcType = JdbcType.INTEGER),
            @Result(column = "lesson_grade", property = "lessonGrade", jdbcType = JdbcType.INTEGER)
    })
    Grade selectByPrimaryKey(Integer gradeId);

    /**
     * 按条件分页查询创建数据集合
     * 多表关联的关系： grade -> lesson & student_manage -> school & class
     *
     * @param rows      每页的行数
     * @param page      页码
     * @param condition 条件参数
     * @return 成绩对象集合
     */
    @Select("SELECT * " +
            " FROM grade g" +
            " JOIN lesson l" +
            " ON g.lesson_id=l.lesson_id" +
            " JOIN student_manage sm" +
            " ON g.student_id=sm.student_id" +
            " JOIN class c" +
            " ON sm.class_id=c.class_id" +
            " JOIN school s" +
            " ON sm.school_id=s.school_id" +
            " join record r" +
            " on r.record_id=sm.record_id" +
            " where 1=1 ${condition} limit #{page},#{rows}")
    @Results({
            @Result(column = "grade_id", property = "gradeId", jdbcType = JdbcType.INTEGER, id = true),
            @Result(column = "lesson_grade", property = "lessonGrade", jdbcType = JdbcType.INTEGER),
            @Result(column = "student_id", property = "studentId", jdbcType = JdbcType.INTEGER),
            @Result(column = "lesson_id", property = "lessonId", jdbcType = JdbcType.INTEGER),
            @Result(column = "student_id", property = "studentManage", javaType = StudentManage.class
                    , one = @One(select = "com.yidu.t299.dao.chr.StudentManageMapper.selectByPrimaryKey")),
            @Result(column = "lesson_id", property = "lesson", javaType = Lesson.class
                    , one = @One(select = "com.yidu.t299.dao.chr.LessonMapper.selectByPrimaryKey"))
    })
    List<Grade> selectByPage(@Param("rows") int rows, @Param("page") int page, @Param("condition") String condition);

    /**
     * 按条件查询grade表中数据行数，因为涉及到了多表，所以这里也需要多表联查，不然无法设置条件
     *
     * @param condition 条件参数
     * @return 数据行数
     */
    @Select("SELECT count(*) " +
            " FROM grade g" +
            " JOIN lesson l" +
            " ON g.lesson_id=l.lesson_id" +
            " JOIN student_manage sm" +
            " ON g.student_id=sm.student_id" +
            " JOIN class c" +
            " ON sm.class_id=c.class_id" +
            " JOIN school s" +
            " ON sm.school_id=s.school_id" +
            " where 1=1 ${condition}")
    int selectCount(@Param("condition") String condition);

    /**
     * 修改成绩信息，因为实体类不能对应，所以要分开传参
     * @param gradeId 成绩编号
     * @param studentId 学员编号
     * @param lessonId 课程编号
     * @param lessonGrade 课程成绩
     * @return 影响的行数(1:成功)
     */
    @Update({
            "update grade ",
            "set student_id = #{studentId,jdbcType=INTEGER},",
            "lesson_id = #{lessonId,jdbcType=INTEGER},",
            "lesson_grade = #{lessonGrade,jdbcType=INTEGER} ",
            "where grade_id = #{gradeId,jdbcType=INTEGER}"
    })
    int updateByPrimaryKey(Integer gradeId,Integer studentId,Integer lessonId,Integer lessonGrade);
}